package com.ki.glasscase.svc.dao.impl;

import com.ki.glasscase.svc.dao.TagDAO;
import com.ki.glasscase.svc.entity.Product;
import com.ki.glasscase.svc.entity.Tag;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataIntegrityViolationException;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;
import java.util.logging.Logger;

/**
 * Created by Khacheresyants.L on 2/18/14.
 */
@Repository("tagDAO")
public class ImplTagDAO implements TagDAO {
    private Logger logger = Logger.getLogger("ImplTagDAO.class");

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    @Override
    public Long create(final Tag tag) {
        Long id = null;
        final String sqlQuery = "INSERT INTO tags (name, translate_name) VALUES (?, ?);";
        try {
            KeyHolder keyHolder = new GeneratedKeyHolder();
            jdbcTemplate.update(new PreparedStatementCreator() {
                @Override
                public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                    PreparedStatement preparedStatement = con.prepareStatement(sqlQuery, new String[]{"id"});
                    preparedStatement.setString(1, tag.getName());
                    preparedStatement.setString(2, tag.getTranslateName());
                    return preparedStatement;
                }
            }, keyHolder);
            id = keyHolder.getKey().longValue();
        } catch (DuplicateKeyException dke) {
            id = getTagByName(tag.getName()).getId();
        } catch (DataIntegrityViolationException e) {
            e.printStackTrace();
        }
        return id;
    }

    @Override
    public Long update(final Tag tag) {
        final String sqlQuery = "UPDATE tags SET, translate_name = ? WHERE  name = ?;";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
                PreparedStatement preparedStatement = con.prepareStatement(sqlQuery, new String[]{"id"});
                preparedStatement.setString(1, tag.getTranslateName());
                preparedStatement.setString(2, tag.getName());

                return preparedStatement;
            }
        }, keyHolder);
        return keyHolder.getKey().longValue();
    }

    @Override
    public Tag getTagByName(String name) {
        String sqlQuery = "SELECT * FROM tags WHERE name = ?;";
        return jdbcTemplate.queryForObject(sqlQuery, new BeanPropertyRowMapper<Tag>(Tag.class), name);
    }

    @Override
    public Tag getTagById(int id) {
        String sqlQuery = "SELECT * FROM tags WHERE id = ?;";
        Tag tag = null;
        try {
            tag = jdbcTemplate.queryForObject(sqlQuery, new BeanPropertyRowMapper<Tag>(Tag.class), id);
        } catch (EmptyResultDataAccessException e) {
            logger.info(e.getLocalizedMessage());
        }
        return tag;
    }

    public List<Tag> getAll(final int LIMIT) {
        String sqlQuery = "SELECT * FROM tags LIMIT ?";
        return jdbcTemplate.query(sqlQuery, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setInt(1, LIMIT);
            }
        }, new BeanPropertyRowMapper<Tag>(Tag.class));
    }

    public List<Tag> getTagByProductId(final Long productId) {
        String sqlQuery = "SELECT t.* FROM tags t JOIN product_tags pt ON t.id = pt.tag_id WHERE pt.product_id = ?;";
        return jdbcTemplate.query(sqlQuery, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
                ps.setLong(1, productId);
            }
        }, new BeanPropertyRowMapper<Tag>(Tag.class));
    }
}
